/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bm.processor;

import com.bm.main.Main;
import com.bm.setting.ServerConfig;
import com.bm.template.IProcessor;
import com.bm.util.DBUtil;
import com.bm.util.Util;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ssm
 */
public class SMSFAL implements IProcessor {

    private JsonObject data = null;

    public SMSFAL(JsonObject data) {
        this.data = data;
    }

    @Override
    public JsonObject getResponse() {
        JsonParser jse = new JsonParser();
        boolean status_transaksi = false;
        String keterangan_transaksi = "";
        int jumlah_sms_dikirim = 0;

        long id_transaksi = insertTransaksi();
        if (id_transaksi != 0) {
            jumlah_sms_dikirim = insertOutbox(id_transaksi);
            if (jumlah_sms_dikirim > 0) {
                if (insertMutasi(id_transaksi, jumlah_sms_dikirim)) {
                    status_transaksi = true;
                    keterangan_transaksi = "SUKSES";
                } else {
                    status_transaksi = false;
                    keterangan_transaksi = "gagal insert mutasi";
                }
            } else {
                status_transaksi = false;
                keterangan_transaksi = "Gagal insert outbox";
            }
        } else {
            status_transaksi = false;
            keterangan_transaksi = "gagal insert transaksi";
        }

        // set respon
        JsonObject jsoResp = new JsonObject();
        jsoResp.add("id_request", data.get("id_request"));
        if (status_transaksi) {
            jsoResp.add("status", jse.parse("1"));
            updateTransaksi(id_transaksi, "00", keterangan_transaksi, jumlah_sms_dikirim);
        } else {
            jsoResp.add("status", jse.parse("-2"));
            updateTransaksi(id_transaksi, "20", keterangan_transaksi, jumlah_sms_dikirim);
        }
        jsoResp.add("keterangan", jse.parse("'" + keterangan_transaksi + "'"));

        return jsoResp;
    }

    private long insertTransaksi() {
        Connection conn = ServerConfig.getConnection();
        PreparedStatement pst = null;
        try {
            String query = "INSERT INTO kamisa.transaksi "
                    + " (id_member,"
                    + " id_produk,"
                    + " hpp,"
                    + " harga_jual,"
                    + " nominal_custom,"
                    + " bill_info1,"
                    + " via,"
                    + " id_request,"
                    + " transaction_date,"
                    + " transaction_time,"
                    + " id_transaksi, "
                    + " bill_info2"
                    + ") "
                    + " values (?,?,?,?,?,?,?,?,now()::date,now()::time,?,?)";
            pst = conn.prepareStatement(query);
            pst.setLong(1, data.get("id_member").getAsLong());
            pst.setString(2, data.get("id_produk").getAsString());
            pst.setLong(3, data.get("hpp").getAsLong());
            pst.setLong(4, data.get("harga_jual").getAsLong());
            pst.setLong(5, data.get("nominal_custom").getAsLong());
            pst.setString(6, data.get("bill_info1").getAsString());
            pst.setString(7, data.get("via").getAsString());
            pst.setLong(8, data.get("id_request").getAsLong());

            long id_transaksi = DBUtil.getLongSeq("kamisa.transaksi_id_transaksi_seq");
            pst.setLong(9, id_transaksi);

            pst.setString(10, data.get("bill_info2").getAsString());

            if (pst.executeUpdate() == 1) {
                return id_transaksi;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SMSFAL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ServerConfig.close(pst);
            ServerConfig.close(conn);
        }
        return 0;
    }

    private boolean insertMutasi(long id_transaksi, int jumlah_sms_dikirim) {
        long nilai_transaksi = (data.get("harga_jual").getAsLong() + data.get("nominal_custom").getAsLong()) * jumlah_sms_dikirim;

        Connection conn = ServerConfig.getConnection();
        PreparedStatement pst = null;
        try {
            String query = "INSERT INTO kamisa.mutasi "
                    + " (id_transaksi,"
                    + " debet,"
                    + " keterangan,"
                    + " tipe_mutasi,"
                    + " id_member,"
                    + " tanggal_mutasi,"
                    + " waktu_mutasi)"
                    + " values (?,?,?,?,?,now()::date,now()::time)";
            pst = conn.prepareStatement(query);
            pst.setLong(1, id_transaksi);
            pst.setLong(2, nilai_transaksi);
            pst.setString(3, "");
            pst.setString(4, data.get("id_produk").getAsString());
            pst.setLong(5, data.get("id_member").getAsLong());
            if (pst.executeUpdate() == 1) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SMSFAL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ServerConfig.close(pst);
            ServerConfig.close(conn);
        }
        return false;
    }

    private int insertOutbox(long id_transaksi) {
        int jumlah_sms_dikirim = data.get("jumlah_beli").getAsInt();
        String isi_sms = data.get("bill_info1").getAsString();
        Connection conn = ServerConfig.getConnection();
        PreparedStatement pst2 = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            String param = data.get("bill_info2").getAsString();
            String id_param = data.get("bill_info3").getAsString();
            String query = "select no_hp from kamisa.data_no_hp where " + param + " in (" + id_param + ") and is_active = 1 limit "+jumlah_sms_dikirim;
            if (param.equalsIgnoreCase("all")){ // semua kota
                query = "select no_hp from kamisa.data_no_hp where is_active = 1 limit "+jumlah_sms_dikirim;
            }
            System.out.println(query);
            pst2 = conn.prepareStatement(query);
            rs = pst2.executeQuery();
            while (rs.next()) {
                String receiver = rs.getString("no_hp");
                query = "INSERT INTO kamisa.message_outbox"
                        + " (id_transaksi,"
                        + " receiver,"
                        + " content,"
                        + " id_modul,"
                        + " is_sent,"
                        + " date_created,"
                        + " id_member,"
                        + " id_request"
                        + ")"
                        + " values (?,?,?,?,0,now(),?,?)";
                pst = conn.prepareStatement(query);
                pst.setLong(1, id_transaksi);
                pst.setString(2, receiver);
                pst.setString(3, isi_sms);
                pst.setString(4, "MI_Processor");
                pst.setLong(5, data.get("id_member").getAsLong());
                pst.setLong(6, data.get("id_request").getAsLong());
                pst.executeUpdate();
            }
        } catch (SQLException ex) {
            Logger.getLogger(SMSFAL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ServerConfig.close(rs);
            ServerConfig.close(pst2);
            ServerConfig.close(pst);
            ServerConfig.close(conn);
        }

        return jumlah_sms_dikirim;
    }

    private boolean updateTransaksi(long id_transaksi, String response_code, String keterangan, int jumlah_sms_dikirim) {
        Connection conn = ServerConfig.getConnection();
        PreparedStatement pst = null;
        try {
            String query = "update kamisa.transaksi set response_code=?, keterangan=?, jumlah_beli=? where id_transaksi=?";
            pst = conn.prepareStatement(query);
            pst.setString(1, response_code);
            pst.setString(2, keterangan);
            pst.setInt(3, jumlah_sms_dikirim);
            pst.setLong(4, id_transaksi);
            pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(SMSFAL.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            ServerConfig.close(pst);
            ServerConfig.close(conn);
        }
        return false;
    }
}
